package org.example.quartz.BIP;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.Session;
import cn.hutool.extra.spring.SpringUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.example.quartz.service.LogService;
import org.example.quartz.service.OrgService;
import org.example.quartz.service.UtilService;
import org.example.quartz.task.QuartzTaskJob;
import org.example.quartz.util.DataSourceUtil;
import org.example.quartz.util.SafeStringUtils;
import org.quartz.DisallowConcurrentExecution;
import org.quartz.JobExecutionContext;
import org.quartz.PersistJobDataAfterExecution;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Component
// 再Job被执行结束后，将会更新JobDataMap，这样下次Job执行后悔使用新的值而不是初始值
@PersistJobDataAfterExecution
// 同一时间将只有一个Job实例被执行，为了避免并发问题导致数据紊乱，建议这两个注解一起使用
@DisallowConcurrentExecution
public class GLTOMIDQuartz_BIP implements QuartzTaskJob {

    Map<String, Object> gsMap = DataSourceUtil.getGS();
    //全局接口配置
    String IP = (String) gsMap.get("IP");
    String PORT = (String) gsMap.get("PORT");
    String ECC = (String) gsMap.get("ECC");
    String Bearer = (String) gsMap.get("Bearer");

    //日志
    LogService loginfo = SpringUtil.getBean(LogService.class);
    //orgService
    OrgService orgService = SpringUtil.getBean(OrgService.class);
    //orgService
    UtilService utilService = SpringUtil.getBean(UtilService.class);

    /**
     * 凭证接口
     * 接口地址：GSCloud文档3.13
     * http://ip:port/api/jg/fiaccdata/v1.0/fiaccinterface/insertPzMidTable
     **/
    @SneakyThrows
    @Override
    public void execute(JobExecutionContext context) {
        try {
            //传入变量 lastTime 最后抓取时间
            //链接BIP数据库，查询出需要同步的数据，根据TS时间进行判断。
            //TS从中间库表获取到最后一次更新时间，根据此时间进行增量查询推送；
            //中间库日志表记录推送日志，成功、失败、返回信息等（备查）
            //中间库记录最后推送数据的时间，根据时间进行下次查询的条件；
            JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
            //查询凭证表GSSTATUS=0的凭证，如果统计的总数大于0不推送
            String gsStatusCount = "SELECT COUNT(1) as TOTAL FROM " + utilService.getGSYY() + ".GL_DETAIL where (GSSTATUS = 0 OR  GSSTATUS IS NULL)";
            List<Map<String, Object>> gsStatusList = jdbcTemplate.queryForList(gsStatusCount);
            int TOTAL = Integer.parseInt(gsStatusList.get(0).get("TOTAL").toString());
            if (TOTAL == 0) {
                //获取当前年和当前年份减一
                List<Integer> years = utilService.getYEARS();
                //String YEAR = utilService.getYEAR();
                //int YEAR = LocalDateTime.now().getYear();
                String orgStr = utilService.getGSOrgStr(jdbcTemplate);
                //拼接为字符串
                StringBuilder YEAR = new StringBuilder();
                for (int i = 0; i < years.size(); i++) {
                    Integer year = years.get(i);
                    //如果不是最后一个需要拼接逗号
                    if (i == years.size() - 1) {
                        YEAR.append("'").append(year).append("'");
                    } else {
                        YEAR.append("'").append(year).append("',");
                    }
                }
                //2、根据时间查询数据集 (停用和删除的过滤掉，暂不考虑，线下处理)
                List<String> iperiodList = new ArrayList<>();
                iperiodList.add("01");
                iperiodList.add("02");
                iperiodList.add("03");
                iperiodList.add("04");
                iperiodList.add("05");
                iperiodList.add("06");
                iperiodList.add("07");
                iperiodList.add("08");
                iperiodList.add("09");
                iperiodList.add("10");
                iperiodList.add("11");
                iperiodList.add("12");
                for (String iperiod : iperiodList) {
                    String methodName = "GLTOMIDQuartz";
                    methodName = methodName + iperiod;
                    //1、查询时间
                    String ts = utilService.getTs(methodName, jdbcTemplate);
                    String selectSQL =
                            "\n" +
                                    "                SELECT--ID\\ID\\VARCHAR2 (36)\\否\\主键 \n" +
                                    "                GL.PK_DETAIL AS ID, \n" +
                                    "                --ORGCODE\\账簿编号\\VARCHAR2 (100)\\否\\ \n" +
                                    "                book.code AS ORGCODE, \n" +
                                    "                --ACCDOCCODE\\凭证编号\\VARCHAR2 (100)\\否\\格式：记000004 \n" +
                                    "                VT.SHORTNAME || GLV.NUM AS ACCDOCCODE, \n" +
                                    "                --ACCDOCDATE\\凭证日期\\VARCHAR2 (8)\\否\\20240405 \n" +
                                    "                GLV.PREPAREDDATE AS ACCDOCDATE, \n" +
                                    "                --ACCDOCTYPECODE\\凭证类型编号\\VARCHAR2 (100)\\否\\ \n" +
                                    "                VT.CODE AS ACCDOCTYPECODE, \n" +
                                    "                --PERIODCODE\\会计期间编号\\VARCHAR2 (2)\\否\\如01 \n" +
                                    "                GLV.PERIOD AS PERIODCODE, \n" +
                                    "                --NUMBEROFATTCH\\附件张数\\INT4\\否\\ \n" +
                                    "                GLV.ATTACHMENT AS NUMBEROFATTCH, \n" +
                                    "                --YEAR\\年度\\VARCHAR2 (4)\\否\\如2024 \n" +
                                    "                GLV.YEAR AS YEAR, \n" +
                                    "                --MAKERCODE\\制单人编号\\VARCHAR2 (100)\\否\\ \n" +
                                    "                sm.user_code AS MAKERCODE, \n" +
                                    "                --OPERATORCODE\\经办人编号\\VARCHAR2 (100)\\是\\默认空 \n" +
                                    "                '' AS OPERATORCODE, \n" +
                                    "                --SECRETLEVEL\\密级\\VARCHAR2 (100)\\是\\默认空 \n" +
                                    "                '' AS SECRETLEVEL, \n" +
                                    "                --APPROVERCODE\\批准人编号\\VARCHAR2 (100)\\是\\默认空 \n" +
                                    "                glv.pk_checked AS APPROVERCODE, \n" +
                                    "                --AUDITORCODE\\审核人编号\\VARCHAR2 (100)\\是\\操作用户编号 \n" +
                                    "                glv.pk_checked AS AUDITORCODE, \n" +
                                    "                --BOOKERCODE\\记账人编号\\VARCHAR2 (100)\\是\\操作用户编号 \n" +
                                    "                glv.pk_manager AS BOOKERCODE, \n" +
                                    "                --CASHIERCODE\\出纳编号\\VARCHAR2 (100)\\是\\操作用户编号 \n" +
                                    "                glv.pk_casher AS CASHIERCODE, \n" +
                                    "                --ACCMANAGERCODE\\会计主管编号\\VARCHAR2 (100)\\是\\操作用户编号 \n" +
                                    "                '' AS ACCMANAGERCODE, \n" +
                                    "                --ISAUDIT\\是否审核\\VARCHAR2 (1)\\是\\1是，0否，默认空 \n" +
                                    "\t\t\t\t\t\t\t\t(CASE WHEN glv.checkeddate  is not null THEN '1' ELSE '0' END ) AS ISAUDIT, \n" +
                                    "                --ISBOOK\\是否记账\\VARCHAR2 (1)\\是\\1是，0否，默认空 \n" +
                                    "                (CASE WHEN glv.tallydate is not null THEN '1' ELSE '0' END ) AS ISBOOK, \n" +
                                    "                --ISVOID\\是否作废\\VARCHAR2 (1)\\是\\1是，0否，默认空 \n" +
                                    "                (CASE WHEN  glv.discardflag ='N' THEN '0' ELSE '1' END ) AS ISVOID, \n" +
                                    // "                glv.discardflag AS ISVOID, \n" +
                                    "                --ISAPPROVED\\是否批准\\VARCHAR2 (1)\\是\\1是，0否，默认空 \n" +
                                    "                '' AS ISAPPROVED, \n" +
                                    "                --EXTERNALDOC\\下级单位凭证编号\\VARCHAR2 (100)\\是\\下级单位凭证编号 \n" +
                                    //"                '' AS EXTERNALDOC, \n" +
                                    "                VT.SHORTNAME || GLV.NUM AS EXTERNALDOC, \n" +
                                    "                --ISREADYONLY\\只读标志\\VARCHAR2 (1)\\是\\1是，0否 \n" +
                                    "                '' AS ISREADYONLY, \n" +
                                    "                --CREATOR\\创建人编号\\VARCHAR2 (100)\\否\\操作用户编号 \n" +
                                    "                sm.user_code AS CREATOR, \n" +
                                    "                --CREATETIME\\创建时间\\TIMESTAMP(6)\\否\\2024-04-07 13:28:35 \n" +
                                    "                GLV.PREPAREDDATE AS CREATETIME, \n" +
                                    "                --LASTMODIFIER\\最后修改人编号\\VARCHAR2 (100)\\否\\操作用户编号 \n" +
                                    //  "                '' AS LASTMODIFIER, \n" +
                                    "                sm5.user_code AS LASTMODIFIER, \n" +
                                    "                --LASTMODIFIEDTIME\\最后修改时间\\TIMESTAMP(6)\\否\\2024-04-07 13:28:35 \n" +
                                    // "                '' AS LASTMODIFIEDTIME, \n" +
                                    "                GLV.modifiedtime AS LASTMODIFIEDTIME, \n" +
                                    "                --SUMMARY\\摘要\\VARCHAR2 (1000)\\否\\ \n" +
                                    "                GL.EXPLANATION AS SUMMARY, \n" +
                                    "                --ACCENTRYCODE\\凭证分录编号\\VARCHAR2 (100)\\否\\00043 \n" +
                                    "                LPAD( GL.DETAILINDEX, 5, '0' ) AS ACCENTRYCODE, \n" +
                                    "                --ACCTITLECODE\\科目编号\\VARCHAR2 (100)\\否\\ \n" +
                                    "                GL.ACCOUNTCODE AS ACCTITLECODE, \n" +
                                    "                --DOCENTRYAMOUNT\\凭证分录金额\\NUMERIC(28,8)\\否\\ \n" +
                                    //  "                ( CASE WHEN GL.DIRECTION = 'D' THEN GL.DEBITAMOUNT ELSE GL.CREDITAMOUNT END ) AS DOCENTRYAMOUNT, \n" +
                                    "                ( CASE WHEN GL.DIRECTION = 'D' THEN GL.LOCALDEBITAMOUNT ELSE GL.LOCALCREDITAMOUNT END ) AS DOCENTRYAMOUNT, \n" +
                                    "                --LENDINGDIRECTION\\记账方向\\VARCHAR2 (1)\\否\\1借，2贷 \n" +
                                    "                ( CASE WHEN GL.DIRECTION = 'D' THEN '借' ELSE '贷' END ) AS LENDINGDIRECTION, \n" +
                                    "                --DEPTID\\核算部门编号\\VARCHAR2 (100)\\是\\0093 \n" +
                                    "                '' AS DEPTID, \n" +
                                    "                --RELATEDORGID\\往来单位编号\\VARCHAR2 (100)\\是\\0934 \n" +
                                    "                '' AS RELATEDORGID, \n" +
                                    "                --ACCEMPLOYEEID\\核算人员编号\\VARCHAR2 (100)\\是\\0921 \n" +
                                    "                '' AS ACCEMPLOYEEID, \n" +
                                    "                --FOREIGNCURRENCYID\\币种编号\\VARCHAR2 (100)\\否\\ \n" +
                                    "                BC.CODE AS FOREIGNCURRENCYID, \n" +
                                    "                --QUANTITY\\数量\\NUMERIC(28,8)\\否\\0 \n" +
                                    "                ( CASE WHEN GL.DIRECTION = 'D' THEN GL.DEBITQUANTITY ELSE GL.CREDITQUANTITY END ) AS QUANTITY, \n" +
                                    "                --UNITPRICE\\单价\\NUMERIC(28,8)\\否\\0 \n" +
                                    "                GL.PRICE AS UNITPRICE, \n" +
                                    "                --FOREIGNCURRENCY\\辅助外币金额\\NUMERIC(28,8)\\否\\ \n" +
                                    // "                ( CASE WHEN GL.DIRECTION = 'D' THEN GL.DEBITAMOUNT ELSE GL.CREDITAMOUNT END ) AS FOREIGNCURRENCY, \n" +
                                    "               ( CASE WHEN GL.DIRECTION = 'D' THEN GL.DEBITAMOUNT ELSE GL.CREDITAMOUNT END ) AS FOREIGNCURRENCY, \n" +
                                    "                --EXCHANGERATE\\汇率\\NUMERIC(28,8)\\否\\ \n" +
                                    "                GL.EXCRATE2 AS EXCHANGERATE, \n" +
                                    "                --ANCILLARYAMOUNT\\辅助金额\\NUMERIC(28,8)\\否\\ \n" +
                                    // "                ( CASE WHEN GL.DIRECTION = 'D' THEN GL.DEBITAMOUNT ELSE GL.CREDITAMOUNT END ) AS ANCILLARYAMOUNT, \n" +
                                    "                 ( CASE WHEN GL.DIRECTION = 'D' THEN GL.LOCALDEBITAMOUNT ELSE GL.LOCALCREDITAMOUNT END ) AS ANCILLARYAMOUNT, \n" +
                                    "                --BIZDATE\\业务日期\\VARCHAR2 (8)\\是\\如20240122 \n" +
                                    "                '' AS BIZDATE, \n" +
                                    "                --BIZCODE\\业务号\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS BIZCODE, \n" +
                                    "                --OPERATOR\\经办人\\VARCHAR2 (100)\\是\\操作用户编号 \n" +
                                    "                '' AS OPERATOR, \n" +
                                    "                --SJ01~SJ05\\自定义数值字段01~自定义数值字段05\\NUMERIC(28,8)\\是\\ \n" +
                                    "                '' AS SJ, \n" +
                                    "                --SM01~SM10\\自定义文本字段01~自定义文本字段10\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS SM, \n" +
                                    "                --SETTLEMENT\\结算方式\\VARCHAR2 (36)\\是\\ \n" +
                                    "                BB.NAME AS SETTLEMENT, \n" +
                                    "                --SETTLEMENTNUMBER\\结算号\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS SETTLEMENTNUMBER, \n" +
                                    "                --APPLICATION\\用途\\VARCHAR2 (1000)\\是\\ \n" +
                                    "                '' AS APPLICATION, \n" +
                                    "                --SETTLEMENTDATE\\结算日期\\VARCHAR2 (8)\\是\\如20240122 \n" +
                                    "                '' AS SETTLEMENTDATE, \n" +
                                    "                --CASHPROJECT\\现金项目\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS CASHPROJECT, \n" +
                                    "                --SPECATEID51~ SPECATEID99\\核算项目51~核算项目99\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS SPECATEID, \n" +
                                    "                --BILLNUMBER\\票据号\\VARCHAR2 (100)\\是\\ \n" +
                                    "                '' AS BILLNUMBER, \n" +
                                    "                --DATASTAUS\\单据状态\\CHAR(1)\\否\\0表示新增，1表示修改，2表示删除 \n" +
                                    "                (CASE WHEN GL.DR = 1 THEN '2' ELSE '0' END) AS DATASTAUS, \n" +
                                    "                --ISIMPORTED\\是否已导入备查实例\\CHAR\\否\\0表示否，1表示是，2表示导入失败 \n" +
                                    "                '0' AS ISIMPORTED, \n" +
                                    "                --FAILREASON\\失败原因\\VARCHAR2(500)\\是\\ \n" +
                                    "                '' AS FAILREASON, \n" +
                                    "                --BATCHNO\\批次号\\VARCHAR2(200)\\否\\账簿编号年月日时分秒拼接，账簿编号20220908010502 \n" +
                                    "                '' AS BATCHNO, \n" +
                                    "                --REFERENCEID\\GSCLOUD凭证ID\\VARCHAR2 (36)\\是\\如果DATASTAUS字段是修改或删除，该字段必填 \n" +
                                    "                '' AS REFERENCEID, \n" +
                                    "                --LYPZID\\下级单位凭证唯一标识\\VARCHAR2 (100)\\否\\ \n" +
                                    "                GL.PK_VOUCHER AS LYPZID, \n" +
                                    "                --FZCODE\\辅助分录编号\\VARCHAR2(50)\\\\如果一个凭证科目分录最多只会有一行辅助数据，该值默认为00001，如果多条按使用流水号即可（00001、00002）。 \n" +
                                    "                '00001' AS FZCODE, \n" +
                                    "                GL.TS AS TS, \n" +
                                    "                BA.DISPNAME AS KMMC, \n" +
                                    "                GL.ASSID AS ASSID  \n" +
                                    "                FROM \n" +
                                    "                " + utilService.getOracle() + ".GL_DETAIL" + utilService.getHZ() + " GL \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".org_accountingbook" + utilService.getHZ() + " book ON gl.pk_accountingbook = book.PK_ACCOUNTINGBOOK \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".ORG_ORGS" + utilService.getHZ() + " ORG ON GL.PK_ORG = ORG.PK_ORG \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".GL_VOUCHER" + utilService.getHZ() + " GLV ON GL.PK_VOUCHER = GLV.PK_VOUCHER \n" +
                                    "\t\t\t\t\t\t\t\t--制单人 \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm ON glv.pk_prepared = sm.cuserid \n" +
                                    "-- \t\t\t\t\t\t\t\t--审核人 \n" +
                                    "-- \t\t\t\t\t\t\t\tLEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm1 ON glv.pk_casher = sm.cuserid \n" +
                                    "-- \t\t\t\t\t\t\t\t--出纳\n" +
                                    "-- \t\t\t\t\t\t\t\tLEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm2 ON glv.pk_checked = sm.cuserid \n" +
                                    "-- \t\t\t\t\t\t\t\t--记账人\n" +
                                    "-- \t\t\t\t\t\t\t\tLEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm3 ON glv.pk_manager = sm.cuserid \n" +
                                    "-- \t\t\t\t\t\t\t\t--操作员 \n" +
                                    "-- \t\t\t\t\t\t\t\tLEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm4 ON glv.creator = sm.cuserid\n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".sm_user" + utilService.getHZ() + " sm5 ON glv.modifier = sm5.cuserid\n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".BD_VOUCHERTYPE" + utilService.getHZ() + " VT ON GLV.PK_VOUCHERTYPE = VT.PK_VOUCHERTYPE \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".BD_CURRTYPE" + utilService.getHZ() + " BC ON GL.PK_CURRTYPE = BC.PK_CURRTYPE \n" +
                                    "                LEFT JOIN " + utilService.getOracle() + ".BD_BALATYPE" + utilService.getHZ() + " BB ON GL.CHECKSTYLE = BB.PK_BALATYPE --LEFT JOIN BD_CASHFLOW BF ON GL.CASHFLOWITEM = BF.PK_CASHFLOW \n" +
                                    "                 LEFT JOIN " + utilService.getOracle() + ".BD_ACCASOA" + utilService.getHZ() + " BA ON BA.PK_accasoa = GL.PK_accasoa \n" +
                                    "                WHERE \n" +
                                    "                ORG.CODE IN (" + orgStr + ") \n" +
                                    // "              ORG.CODE IN ('1001') \n" +
                                    "                AND \n" +
                                    "                GLV.YEAR in (" + YEAR + ")\n" +
                                    "                AND GLV.PERIOD = '" + iperiod + "'\n" +
                                    "                AND GLV.voucherkind <> 2  \n" +
                                    "                AND GLV.voucherkind <> 255  \n" +
                                    "                AND GLV.dr = 0  \n" +
                                    "                AND ( GLV.voucherkind = 5  AND GLV.adjustperiod <> '00' OR GLV.voucherkind <> 5  ) \n" +
                                    "                AND GL.TS >'" + ts + "' \n" +
                                    "                 ORDER BY GL.TS ASC";
                    //查询数据，根据数据结果推送给GS浪潮
                    Session oracleSession = DataSourceUtil.getOracleSession();
                    List<Entity> query = oracleSession.query(selectSQL);
                    //将查询的数据按照凭证号、会计期间、年份进行分组（单张凭证推送中间库）
                    System.out.println(iperiod + "月凭证数量为:" + query.size());
                    //从 query 集合中读取数据，并根据指定的键（ ACCDOCCODE, PERIODCODE, 和 YEAR 的组合）对数据进行分组
                    Map<String, List<Entity>> voucherid = query.stream().collect(Collectors.groupingBy(i -> i.get("ACCDOCCODE") + "," + i.get("PERIODCODE") + "," + i.get("YEAR"), LinkedHashMap::new, Collectors.toList()));
                    for (Map.Entry<String, List<Entity>> objectListEntry : voucherid.entrySet()) {
                        //sql添加脚本
                        String insertSQL = "";
                        //sql最后修改时间
                        String logTS = "";
                        //凭证编号
                        String voucherCode = "";
                        //循环分组，数据处理
                        List<Entity> value = objectListEntry.getValue();
                        if (value.size() > 0) {
                            //定义集合正常
                            List<Entity> normal = new ArrayList<>();
                            //删除集合
                            List<Entity> delete = new ArrayList<>();
                            //按照LYPZID分组
                            Map<String, List<Entity>> lypzids = value.stream().collect(Collectors.groupingBy(i -> i.get("LYPZID").toString(), LinkedHashMap::new, Collectors.toList()));
                            for (Map.Entry<String, List<Entity>> lypzidListEntry : lypzids.entrySet()) {
                                //是否正常表示
                                boolean isNormal = true;
                                List<Entity> entities = lypzidListEntry.getValue();
                                //如果当前集合中Datastatus字段 =0的数据条数大于1
                                if (entities.stream().filter(i -> "0".equals(i.get("DATASTAUS"))).count() > 1) {
                                    normal.addAll(entities);
                                }else{
                                    delete.addAll(entities);
                                }

                                //分录数据处理
//                                for (Map<String, Object> gSmap : entities) {
//                                    String DATASTAUS = (String) gSmap.get("DATASTAUS");
//                                    //数据区分：区分正常凭证以及删除凭证，
//                                    if (!"2".equals(DATASTAUS)) {
//                                        isNormal = true;
//                                        normal.addAll(entities);
//                                        continue;
//                                    } else {
//                                        isNormal = false;
//                                    }
//                                }
                                //不等于true说明是删除凭证
//                                if (!isNormal) {
//                                    delete.addAll(entities);
//                                }
                            }
                            //正常凭证处理
                            if (normal.size() > 0) {
                                for (Map<String, Object> gSmap : normal) {
                                    String DATASTAUS = (String) gSmap.get("DATASTAUS"); //0=新增 1-修改  3=特殊数据
                                    //正常凭证如果DATASTATSUS=2说明分录已经删除不需要添加到数据库
                                    if (DATASTAUS.equals("2")) {continue;}
                                    String REFERENCEID = ""; //1-修改 2=删除 该字段需要有值
                                    String PUSHSTATUS = "0"; //推送状态 0=未推送 1=已推送 3=特殊数据
                                    String GSSTATE = "0"; //回写状态 0=未回写 1=已回写 2=失败 3=特殊数据
                                    String RETURNMSG = ""; //回写信息：
                                    String LASTMODIFIER = (String) gSmap.get("CREATOR"); //最后修改人
                                    String LASTMODIFIEDTIME = (String) gSmap.get("CREATETIME"); //最后修时间
                                    //根据LYPZID查询中间库获取REFERENCEID，GSSTATUS
                                    String selectAddOrUpdate = "select ID,REFERENCEID,GSSTATUS,LYPZID FROM " + utilService.getGSYY() + ".GL_DETAIL WHERE LYPZID = '" + gSmap.get("LYPZID") + "' ORDER BY TS DESC";
                                    List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectAddOrUpdate);
                                    //maps不为NULL，并且GSSTATUS是1说明推送成功，需要按照修改从新推送
                                    if (maps.size() > 0) {
                                        String GSSTATUS = (String) maps.get(0).get("GSSTATUS");
                                        if ("1".equals(GSSTATUS)) {
                                            DATASTAUS = "1"; //修改
                                            REFERENCEID = (String) maps.get(0).get("REFERENCEID");
                                            LASTMODIFIER = (String) gSmap.get("LASTMODIFIER");
                                            LASTMODIFIEDTIME = (String) gSmap.get("LASTMODIFIEDTIME");
                                        }
                                    }
                                    insertSQL += this.sqlAssembly(gSmap, DATASTAUS, REFERENCEID, PUSHSTATUS, GSSTATE, RETURNMSG, LASTMODIFIER, LASTMODIFIEDTIME);
                                    logTS = (String) gSmap.get("TS");
                                    voucherCode = (String) gSmap.get("ACCDOCCODE");
                                }
                            }
                            //删除凭证处理
                            if (delete.size() > 0) {
                                for (Map<String, Object> gSmap : delete) {
                                    String DATASTAUS = (String) gSmap.get("DATASTAUS"); //0=新增 1-修改  3=特殊数据
                                    String REFERENCEID = ""; //1-修改 2=删除 该字段需要有值
                                    String PUSHSTATUS = "0"; //推送状态 0=未推送 1=已推送 3=特殊数据
                                    String GSSTATE = "0"; //回写状态 0=未回写 1=已回写 2=失败 3=特殊数据
                                    String RETURNMSG = ""; //回写信息：
                                    String LASTMODIFIER = (String) gSmap.get("CREATOR"); //最后修改人
                                    String LASTMODIFIEDTIME = (String) gSmap.get("CREATETIME"); //最后修时间
                                    //根据LYPZID查询中间库获取REFERENCEID，GSSTATUS
                                    String selectAddOrUpdate = "select ID,REFERENCEID,GSSTATUS,LYPZID FROM " + utilService.getGSYY() + ".GL_DETAIL WHERE LYPZID = '" + gSmap.get("LYPZID") + "' ORDER BY TS DESC";
                                    List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectAddOrUpdate);
                                    //如果maps等于空跳过本次循环
                                    if (maps.size() == 0) {continue;}
                                    //GSSTATUS = 1说明推送成功，2说明推送失败
                                    String GSSTATUS = (String) maps.get(0).get("GSSTATUS");
                                    if ("1".equals(GSSTATUS)) {
                                        DATASTAUS = "2";    //删除
                                        REFERENCEID = (String) maps.get(0).get("REFERENCEID");
                                        LASTMODIFIER = (String) gSmap.get("LASTMODIFIER");
                                        LASTMODIFIEDTIME = (String) gSmap.get("LASTMODIFIEDTIME");
                                    } else if ("2".equals(GSSTATUS)) {
                                        DATASTAUS = "3";
                                        PUSHSTATUS = "3";
                                        GSSTATE = "3";
                                        RETURNMSG = "GS生成凭证失败,但是用友凭证已删除,该凭证只做记录";
                                        LASTMODIFIER = (String) gSmap.get("LASTMODIFIER");
                                        LASTMODIFIEDTIME = (String) gSmap.get("LASTMODIFIEDTIME");
                                    }
                                    insertSQL += this.sqlAssembly(gSmap, DATASTAUS, REFERENCEID, PUSHSTATUS, GSSTATE, RETURNMSG, LASTMODIFIER, LASTMODIFIEDTIME);
                                }
                            }
                        }
                        int update = 0;
                        if (StrUtil.isNotBlank(insertSQL)) {
                            String sql = "BEGIN\n";
                            sql += insertSQL;
                            sql += "\nEND;";
                            //执行语句
                            update = jdbcTemplate.update(sql);
                        }
                        //成功
                        if (update > 0) {
                            int loginsert = loginfo.loginfo(jdbcTemplate, methodName, LocalDateTime.now().toString(), "成功", "入参", "出参", "", voucherCode, "insertMID", logTS);
                            if (loginsert > 0) {
                                //System.out.println("日志写入成功！！");
                            } else {
                                System.out.println("日志写入失败！！");
                            }
                            //失败
                        } else {
                            //写入日志表
                            int loginsert = loginfo.loginfo(jdbcTemplate, methodName, LocalDateTime.now().toString(), "失败", "入参", "出参", "", voucherCode, "insertMID", logTS);
                            if (loginsert > 0) {
                                //System.out.println("日志写入成功！！");
                            } else {
                                System.out.println("日志写入失败！！");
                            }
                        }
                    }
                }
            } else {
                System.out.println("有未回写的凭证：(" + TOTAL + ")条");
            }

        } catch (Exception e) {
            System.out.println(e.toString());
            throw new RuntimeException(e);
        } finally {
            //关闭链接
            DataSourceUtil.closeOracleSession();
            DataSourceUtil.closeMysqlSession();
        }
    }


    /**
     * sql组装
     *
     * @param gSmap
     * @param DATASTAUS
     * @param REFERENCEID
     * @param PUSHSTATUS
     * @param GSSTATE
     * @param RETURNMSG
     * @param LASTMODIFIER
     * @param LASTMODIFIEDTIME
     * @return
     */
    private String sqlAssembly(Map<String, Object> gSmap, String DATASTAUS, String REFERENCEID, String PUSHSTATUS, String GSSTATE, String RETURNMSG, String LASTMODIFIER, String LASTMODIFIEDTIME) {
        //判断附件张数是否为空
        String NUMBEROFATTCH = ObjectUtils.isEmpty(gSmap.get("NUMBEROFATTCH")) ? "0" : gSmap.get("NUMBEROFATTCH").toString();
        String EXCHANGERATE = ObjectUtils.isEmpty(gSmap.get("EXCHANGERATE")) ? "0" : gSmap.get("EXCHANGERATE").toString();
        //科目编码
        String ACCTITLECODE = gSmap.get("ACCTITLECODE").toString();
        if (!ObjectUtils.isEmpty(gSmap.get("KMMC"))) {
            String path = gSmap.get("KMMC").toString();
            // 查找第一个反斜杠的位置
            int index = path.indexOf('\\');
            if (index != -1) {
                // 截取第一个反斜杠之前的内容
                ACCTITLECODE = path.substring(0, index);

            }
        }
        String insertSQL = "INSERT INTO " + utilService.getGSYY() + ".gl_detail (\n" +
                "MID,\n" +
                "ID,\n" +
                "ORGCODE,\n" +
                "ACCDOCCODE,\n" +
                "ACCDOCDATE,\n" +
                "ACCDOCTYPECODE,\n" +
                "PERIODCODE,\n" +
                "NUMBEROFATTCH,\n" +
                "YEAR,\n" +
                "MAKERCODE,\n" +
                "OPERATORCODE,\n" +
                "SECRETLEVEL,\n" +
                "APPROVERCODE,\n" +
                "AUDITORCODE,\n" +
                "BOOKERCODE,\n" +
                "CASHIERCODE,\n" +
                "ACCMANAGERCODE,\n" +
                "ISAUDIT,\n" +
                "ISBOOK,\n" +
                "ISVOID,\n" +
                "ISAPPROVED,\n" +
                "EXTERNALDOC,\n" +
                "ISREADYONLY,\n" +
                "CREATOR,\n" +
                "CREATETIME,\n" +
                "LASTMODIFIER,\n" +
                "LASTMODIFIEDTIME,\n" +
                "SUMMARY,\n" +
                "ACCENTRYCODE,\n" +
                "ACCTITLECODE,\n" +
                "DOCENTRYAMOUNT,\n" +
                "LENDINGDIRECTION,\n" +
                "DEPTID,\n" +
                "RELATEDORGID,\n" +
                "ACCEMPLOYEEID,\n" +
                "FOREIGNCURRENCYID,\n" +
                "QUANTITY,\n" +
                "UNITPRICE,\n" +
                "FOREIGNCURRENCY,\n" +
                "EXCHANGERATE,\n" +
                "ANCILLARYAMOUNT,\n" +
                "BIZDATE,\n" +
                "BIZCODE,\n" +
                "OPERATOR,\n" +
                "SJ,\n" +
                "SM,\n" +
                "SETTLEMENT,\n" +
                "SETTLEMENTNUMBER,\n" +
                "APPLICATION,\n" +
                "SETTLEMENTDATE,\n" +
                "CASHPROJECT,\n" +
                "SPECATEID,\n" +
                "BILLNUMBER,\n" +
                "DATASTAUS,\n" +
                "ISIMPORTED,\n" +
                "FAILREASON,\n" +
                "BATCHNO,\n" +
                "REFERENCEID,\n" +
                "LYPZID,\n" +
                "FZCODE,\n" +
                "TS,\n" +
                "ASSID,\n" +
                "YYZTCODE,\n" +
                "RETURNMSG,\n" +
                "GSSTATUS,\n" +
                "PUSHSTATUS" +
                ")\n" +
                "VALUES\n" +
                "( " +
                "SYS_GUID(), \n" +
                "'" + gSmap.get("ID") + "', \n" +
                "'" + gSmap.get("ORGCODE") + "', \n" +
                "'" + gSmap.get("ACCDOCCODE") + "', \n" +
                "'" + gSmap.get("ACCDOCDATE") + "', \n" +
                "'" + gSmap.get("ACCDOCTYPECODE") + "', \n" +
                "'" + gSmap.get("PERIODCODE") + "', \n" +
                "'" + NUMBEROFATTCH + "', \n" +
                "'" + gSmap.get("YEAR") + "', \n" +
                "'" + gSmap.get("MAKERCODE") + "', \n" +
                "'" + gSmap.get("OPERATORCODE") + "', \n" +
                "'" + gSmap.get("SECRETLEVEL") + "', \n" +
                "'" + gSmap.get("APPROVERCODE") + "', \n" +
                "'" + gSmap.get("AUDITORCODE") + "', \n" +
                "'" + gSmap.get("BOOKERCODE") + "', \n" +
                "'" + gSmap.get("CASHIERCODE") + "', \n" +
                "'" + gSmap.get("ACCMANAGERCODE") + "', \n" +
                "'" + gSmap.get("ISAUDIT") + "', \n" +
                "'" + gSmap.get("ISBOOK") + "', \n" +
                "'" + gSmap.get("ISVOID") + "', \n" +
                "'" + gSmap.get("ISAPPROVED") + "', \n" +
                "'" + gSmap.get("EXTERNALDOC") + "', \n" +
                "'" + gSmap.get("ISREADYONLY") + "', \n" +
                "'" + gSmap.get("CREATOR") + "', \n" +
                "'" + gSmap.get("CREATETIME") + "', \n" +
                "'" + LASTMODIFIER + "', \n" +
                "'" + LASTMODIFIEDTIME + "', \n" +
                "'" + SafeStringUtils.escapeSpecialChars(gSmap.get("SUMMARY").toString()) + "', \n" +
                "'" + gSmap.get("ACCENTRYCODE") + "', \n" +
                "'" + ACCTITLECODE + "', \n" +
                "'" + gSmap.get("DOCENTRYAMOUNT") + "', \n" +
                "'" + gSmap.get("LENDINGDIRECTION") + "', \n" +
                "'" + gSmap.get("DEPTID") + "', \n" +
                "'" + gSmap.get("RELATEDORGID") + "', \n" +
                "'" + gSmap.get("ACCEMPLOYEEID") + "', \n" +
                "'" + gSmap.get("FOREIGNCURRENCYID") + "', \n" +
                "'" + gSmap.get("QUANTITY") + "', \n" +
                "'" + gSmap.get("UNITPRICE") + "', \n" +
                "'" + gSmap.get("FOREIGNCURRENCY") + "', \n" +
                "'" + EXCHANGERATE + "', \n" +
                "'" + gSmap.get("ANCILLARYAMOUNT") + "', \n" +
                "'" + gSmap.get("BIZDATE") + "', \n" +
                "'" + gSmap.get("BIZCODE") + "', \n" +
                "'" + gSmap.get("OPERATOR") + "', \n" +
                "'" + gSmap.get("SJ") + "', \n" +
                "'" + gSmap.get("SM") + "', \n" +
                "'" + gSmap.get("SETTLEMENT") + "', \n" +
                "'" + gSmap.get("SETTLEMENTNUMBER") + "', \n" +
                "'" + gSmap.get("APPLICATION") + "', \n" +
                "'" + gSmap.get("SETTLEMENTDATE") + "', \n" +
                "'" + gSmap.get("CASHPROJECT") + "', \n" +
                "'" + gSmap.get("SPECATEID") + "', \n" +
                "'" + gSmap.get("BILLNUMBER") + "', \n" +
                "'" + DATASTAUS + "', \n" +
                "'" + gSmap.get("ISIMPORTED") + "', \n" +
                "'" + gSmap.get("FAILREASON") + "', \n" +
                "'" + gSmap.get("BATCHNO") + "', \n" +
                "'" + REFERENCEID + "', \n" +
                "'" + gSmap.get("LYPZID") + "', \n" +
                "'" + gSmap.get("FZCODE") + "', \n" +
                "'" + gSmap.get("TS") + "'," +
                "'" + gSmap.get("ASSID") + "'," +
                "'" + gSmap.get("ORGCODE") + "'," +
                "'" + RETURNMSG + "'," +
                "'" + GSSTATE + "'," +
                "'" + PUSHSTATUS + "'" +
                ");";
        return insertSQL;
    }
}
